package com.es.business.area;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class AreaBiz {
	
	public boolean insertArea(String code, String value) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "insert into hrm_area (area_code, area_value) values(?,?)";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		sqlParam.add(value);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public PageBean searchArea(String code, String value, int curpage) throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = "select area_id, area_code, area_value from hrm_area ";
		String[] sqlp = {" area_code = ? ", " area_value = ? "};
		String and = " and ";
		String where = " where ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam2.add(code);
		sqlParam2.add(value);
		boolean flag = false;
		if(code != null && code.length() > 0){
			if(flag){
				sqlParam.add(code);
				sql += and + sqlp[0];
				
			}else{
				sqlParam.add(code);
				sql += where + sqlp[0];
				flag = true;
			}
		}
		if(value != null && value.length() > 0){
			if(flag){
				sqlParam.add(value);
				sql += and + sqlp[1];
			}else{
				sqlParam.add(value);
				flag = true;
			}
		}
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public boolean deleteAreaById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "delete from hrm_area where area_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public AreaBean getAreaById(String id) throws Exception{
		Dao dao = new Dao();
		AreaBean result = null;
		String sql = "select area_code, area_value from hrm_area where area_id =? ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result = new AreaBean();
				result.setAreaId(id);
				result.setAreaCode(rs.getString(1));
				result.setAreaValue(rs.getString(2));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public AreaBean getAreaByCode(String code) throws Exception{
		Dao dao = new Dao();
		AreaBean result = new AreaBean();;
		String sql = "select area_id, area_value from hrm_area where area_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result.setAreaId(rs.getString(1));
				result.setAreaCode(code);
				result.setAreaValue(rs.getString(2));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean modifyAreaById(String code, String value, String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "update hrm_area set area_code =?, area_value =? where area_id =?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		sqlParam.add(value);
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<AreaBean> getAreaList() throws Exception{
		Dao dao = new Dao();
		ArrayList<AreaBean> result = new ArrayList<AreaBean>();
		String sql = "select area_id, area_code, area_value from hrm_area";
		ArrayList<String> sqlParam = new ArrayList<String>();
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				AreaBean areaBean = new AreaBean();
				areaBean.setAreaId(rs.getString(1));
				areaBean.setAreaCode(rs.getString(2));
				areaBean.setAreaValue((rs.getString(3)));
				result.add(areaBean);
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}

	public ArrayList<AreaBean> getRTMAreaByCode(String area_code) throws Exception {
		// TODO Auto-generated method stub
		Dao dao = new Dao();
		ArrayList<AreaBean> result = new ArrayList<AreaBean>();
		String sql = "select a.area_id, a.area_code, a.area_value from hrm_area a where area_code=? ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(area_code);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				AreaBean areaBean = new AreaBean();
				areaBean.setAreaId(rs.getString(1));
				areaBean.setAreaCode(rs.getString(2));
				areaBean.setAreaValue((rs.getString(3)));
				result.add(areaBean);
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
}
